from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func
from app.models import NetworkVolumeObservation, NetworkVolumeAggregate


async def compute_aggregates(session: AsyncSession, activity_id: str) -> dict:
    media_outlets = 0
    media_reports = 0
    social_interactions = 0
    index_growth = 0.0
    row_q = await session.execute(
        select(NetworkVolumeAggregate).where(
            NetworkVolumeAggregate.activity_id == activity_id,
            NetworkVolumeAggregate.metric_key == 'index_growth_rate'
        )
    )
    row = row_q.scalars().first()
    if row and row.value is not None:
        try:
            index_growth = float(row.value)
        except Exception:
            index_growth = 0.0
    result = await session.execute(
        select(func.count(func.distinct(NetworkVolumeObservation.author_or_account)))
        .where(
            NetworkVolumeObservation.activity_id == activity_id,
            NetworkVolumeObservation.platform == 'news',
            NetworkVolumeObservation.metric_type == 'media_mention'
        )
    )
    media_outlets = int(result.scalar() or 0)
    result = await session.execute(
        select(func.count(NetworkVolumeObservation.id))
        .where(
            NetworkVolumeObservation.activity_id == activity_id,
            NetworkVolumeObservation.platform == 'news',
            NetworkVolumeObservation.metric_type == 'media_mention'
        )
    )
    media_reports = int(result.scalar() or 0)
    result = await session.execute(
        select(func.coalesce(func.sum(NetworkVolumeObservation.value), 0))
        .where(
            NetworkVolumeObservation.activity_id == activity_id,
            NetworkVolumeObservation.platform.in_(['weibo','douyin','kuaishou','xhs','wechat']),
            NetworkVolumeObservation.metric_type.in_(['likes','comments','shares','favorites','impressions'])
        )
    )
    social_interactions = float(result.scalar() or 0)
    details = {}
    for platform in ['weibo','douyin','kuaishou','xhs','wechat']:
        r = await session.execute(
            select(func.coalesce(func.sum(NetworkVolumeObservation.value), 0))
            .where(
                NetworkVolumeObservation.activity_id == activity_id,
                NetworkVolumeObservation.platform == platform,
                NetworkVolumeObservation.metric_type.in_(['likes','comments','shares','favorites','impressions'])
            )
        )
        details[platform] = float(r.scalar() or 0)
    aggregates = {
        'media_unique_outlets': float(media_outlets),
        'media_report_count': float(media_reports),
        'social_total_interactions': float(social_interactions),
        'index_growth_rate': float(index_growth),
    }
    for k, v in aggregates.items():
        obj = await session.execute(
            select(NetworkVolumeAggregate).where(
                NetworkVolumeAggregate.activity_id == activity_id,
                NetworkVolumeAggregate.metric_key == k
            )
        )
        row = obj.scalars().first()
        if row:
            row.value = v
            if k == 'social_total_interactions':
                row.details_json = str(details)
        else:
            dj = str(details) if k == 'social_total_interactions' else None
            row = NetworkVolumeAggregate(activity_id=activity_id, metric_key=k, value=v, details_json=dj)
            session.add(row)
    await session.flush()
    await session.commit()
    return aggregates
